import sqlite3
import os
from logger_config import logger


class Database:
    def __init__(self, db_name="deploy_tool.db"):
        self.db_name = db_name
        self.conn = None
        self.cursor = None
        self.init_db()

    def connect(self):
        """建立数据库连接，如果已连接则复用"""
        if self.conn is not None:
            return True  # 已经连接

        try:
            self.conn = sqlite3.connect(self.db_name)
            self.cursor = self.conn.cursor()
            return True
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            self.conn = None
            self.cursor = None
            return False

    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            self.conn = None
            self.cursor = None

    def init_db(self):
        """初始化数据库表结构 - 移除服务器表的private_key字段"""
        if not self.connect():
            return

        try:
            # 创建服务器表 - 已移除private_key字段
            self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS servers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                ip TEXT NOT NULL,
                port INTEGER NOT NULL DEFAULT 22,
                username TEXT NOT NULL,
                password TEXT,
                desc TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            ''')

            # 创建项目表
            self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS projects (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                server_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                type TEXT NOT NULL, -- vue 或 jar
                local_path TEXT,
                remote_path TEXT NOT NULL,
                start_command TEXT,
                stop_command TEXT,
                desc TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (server_id) REFERENCES servers (id) ON DELETE CASCADE
            )
            ''')

            # 创建数据库配置表
            self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS db_configs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                server_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                type TEXT NOT NULL, -- mysql, postgresql 等
                host TEXT NOT NULL,
                port INTEGER NOT NULL,
                database TEXT NOT NULL,
                username TEXT NOT NULL,
                password TEXT,
                desc TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (server_id) REFERENCES servers (id) ON DELETE CASCADE
            )
            ''')

            self.conn.commit()
        except Exception as e:
            logger.error(f"初始化数据库失败: {str(e)}")
            self.conn.rollback()
        finally:
            self.close()

    def execute(self, sql, params=None, close_conn=True):
        """执行SQL语句，可控制是否自动关闭连接"""
        if not self.connect():
            return None

        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            self.conn.commit()
            return self.cursor
        except Exception as e:
            logger.error(f"SQL执行失败: {str(e)}, SQL: {sql}, 参数: {params}")
            self.conn.rollback()
            return None
        finally:
            if close_conn:
                self.close()

    def fetch_all(self, sql, params=None):
        """获取所有查询结果"""
        # 执行查询时不关闭连接，以便获取结果
        cursor = self.execute(sql, params, close_conn=False)
        if not cursor:
            self.close()
            return []

        try:
            return cursor.fetchall()
        except Exception as e:
            logger.error(f"获取查询结果失败: {str(e)}")
            return []
        finally:
            self.close()  # 确保最终关闭连接

    def fetch_one(self, sql, params=None):
        """获取单条查询结果"""
        # 执行查询时不关闭连接，以便获取结果
        cursor = self.execute(sql, params, close_conn=False)
        if not cursor:
            self.close()
            return None

        try:
            return cursor.fetchone()
        except Exception as e:
            logger.error(f"获取查询结果失败: {str(e)}")
            return None
        finally:
            self.close()  # 确保最终关闭连接

    # 服务器相关操作 - 已移除private_key相关参数
    def get_server_list(self):
        return self.fetch_all("SELECT id, name, ip, port, username, desc FROM servers ORDER BY name")

    def get_server_by_id(self, server_id):
        return self.fetch_one("SELECT * FROM servers WHERE id = ?", (server_id,))

    def add_server(self, name, ip, port, username, password, desc):
        return self.execute(
            "INSERT INTO servers (name, ip, port, username, password, desc) VALUES (?, ?, ?, ?, ?, ?)",
            (name, ip, port, username, password, desc)
        )

    def update_server(self, server_id, name, ip, port, username, password, desc):
        return self.execute(
            "UPDATE servers SET name = ?, ip = ?, port = ?, username = ?, password = ?, desc = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
            (name, ip, port, username, password, desc, server_id)
        )

    def delete_server(self, server_id):
        return self.execute("DELETE FROM servers WHERE id = ?", (server_id,))

    # 项目相关操作（保持不变）
    def get_projects_by_server(self, server_id):
        return self.fetch_all(
            "SELECT id, name, type, local_path, remote_path, start_command, stop_command, desc FROM projects WHERE server_id = ? ORDER BY name",
            (server_id,)
        )

    def get_project_by_id(self, project_id):
        return self.fetch_one("SELECT * FROM projects WHERE id = ?", (project_id,))

    def add_project(self, server_id, name, type_, local_path, remote_path, start_command, stop_command, desc):
        return self.execute(
            """INSERT INTO projects (server_id, name, type, local_path, remote_path, start_command, 
            stop_command, desc) VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (server_id, name, type_, local_path, remote_path, start_command, stop_command, desc)
        )

    def update_project(self, project_id, server_id, name, type_, local_path, remote_path, start_command, stop_command,
                       desc):
        return self.execute(
            """UPDATE projects SET server_id = ?, name = ?, type = ?, local_path = ?, remote_path = ?, 
            start_command = ?, stop_command = ?, desc = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?""",
            (server_id, name, type_, local_path, remote_path, start_command, stop_command, desc, project_id)
        )

    def delete_project(self, project_id):
        return self.execute("DELETE FROM projects WHERE id = ?", (project_id,))

    # 数据库配置相关操作（保持不变）
    def get_db_configs_by_server(self, server_id):
        return self.fetch_all(
            "SELECT id, name, type, host, port, database, username, password, desc FROM db_configs WHERE server_id = ? ORDER BY name",
            (server_id,)
        )

    def get_db_config_by_id(self, config_id):
        return self.fetch_one("SELECT * FROM db_configs WHERE id = ?", (config_id,))

    def add_db_config(self, server_id, name, type_, host, port, database, username, password, desc):
        return self.execute(
            """INSERT INTO db_configs (server_id, name, type, host, port, database, username, password, desc) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
            (server_id, name, type_, host, port, database, username, password, desc)
        )

    def update_db_config(self, config_id, server_id, name, type_, host, port, database, username, password, desc):
        return self.execute(
            """UPDATE db_configs SET server_id = ?, name = ?, type = ?, host = ?, port = ?, database = ?, 
            username = ?, password = ?, desc = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?""",
            (server_id, name, type_, host, port, database, username, password, desc, config_id)
        )

    def delete_db_config(self, config_id):
        return self.execute("DELETE FROM db_configs WHERE id = ?", (config_id,))
